import sqlite3
import os

from datastore import DataStore

class SqliteDataStore(DataStore):
    def __init__(self, debug = False):
        self.debug = debug
        self.connection_map = {}

    def initialize(self, obj):
        if not self.connection_map.has_key(obj.__class__.__name__):
            self.db_name = "{0}.db".format(obj.__class__.__name__)
            connection = sqlite3.connect(os.path.expanduser(os.path.join("~","db",self.db_name)))
            connection.row_factory = sqlite3.Row
            cursor = connection.cursor()
            self.connection_map[obj.__class__.__name__] = (connection, cursor)

    def resolve_type(self, obj):
        if self.connection_map.has_key(obj.__class__.__name__):
            return self.connection_map[obj.__class__.__name__]

    def mapping(self, obj_type):
        result = ""
        if isinstance(obj_type, int):
            result = "INTEGER"
        if isinstance(obj_type, str):
            result = "text"
        if isinstance(obj_type, float):
            result = "real"
        return result

    def pythonMapping(self, obj):
        if isinstance(obj, int) or isinstance(obj, float):
            return obj
        return "'{0}'".format(obj)

    def create_schema(self, obj):
        connection, cursor = self.resolve_type(obj)

        if self.debug:
            print "creating schema for {0} with keys".format(obj.__class__.__name__)
            for k in obj.__dict__.keys():
                print k

        table_name = obj.__class__.__name__
        if self.debug:
            print "table_name :", table_name

        columns_list = ["{0} {1}".format(key,self.mapping(obj.__dict__[key])) for key in obj.__dict__.keys()]
        columns_list.append("PRIMARY KEY({0})".format(obj.key()))
        if self.debug:
            print "columns_list :", str(columns_list)

        column_string = ",".join(columns_list)
        if self.debug:
            print "column_string :", column_string

        create_table_sql_statement = "create table if not exists {0} ({1})".format(table_name, column_string)
        if self.debug:
            print "create_table_sql_statement : ", create_table_sql_statement

        try:
            cursor.execute(create_table_sql_statement)
            connection.commit()
        except Exception as e:
            print e


    def delete_schema(self, obj):
        connection, cursor = self.resolve_type(obj)

        if self.debug:
            print "deleting schema for {0} with keys".format(obj.__class__.__name__)
            for k in obj.__dict__.keys():
                print k

        table_name = obj.__class__.__name__
        if self.debug:
            print "table_name :", table_name

        drop_table_sql_statement = "drop table if exists {0}".format(table_name)
        if self.debug:
            print "drop_table_sql_statement : ", drop_table_sql_statement

        try:
            cursor.execute(drop_table_sql_statement)
            connection.commit()
        except Exception as e:
            print e

    def save(self, obj):
        connection, cursor = self.resolve_type(obj)

        if self.debug:
            print "saving obj to {0} with keys".format(obj.__class__.__name__)
            for k in obj.__dict__.keys():
                print k,obj.__dict__[k]

        table_name = obj.__class__.__name__
        if self.debug:
            print "db name: " , table_name

        parameters = ",".join(list("?"*len(obj.__dict__.keys())))
        if self.debug:
            print "parameters : ", parameters

        prepared_sql_statement = "replace into {0} values ({1})".format(table_name, parameters)
        if self.debug:
           print "prepared_sql_statement : ", prepared_sql_statement
        
        values = [obj.__dict__[key] for key in obj.__dict__.keys()]
        values = tuple(values)
        if self.debug:
            print "values : ", values
        
        try:
            cursor.execute(prepared_sql_statement, values)
            connection.commit()
        except Exception as e:
            print e


    def get(self, obj, attributes = {}, count = 1):
        connection, cursor = self.resolve_type(obj)

        if self.debug:
            print "getting obj having schema {0} with keys".format(obj.__class__.__name__)
            for k in obj.__dict__.keys():
                print k,obj.__dict__[k]

        table_name = obj.__class__.__name__
        if self.debug:
            print "db name: " , table_name

        key_value_pair_list = ["{0}={1}".format(key, self.pythonMapping(obj.__dict__[key])) for key in obj.__dict__.keys() ]
        if self.debug:
            print "key_value_pair_list :", str(key_value_pair_list)

        key_value_pair_string = " and ".join(key_value_pair_list)
        if self.debug:
            print "key_value_pair_string : ", key_value_pair_string

        column_names = ",".join(obj.__dict__.keys())

        prepared_sql_statement = "select {0} from {1} where {2}".format(column_names,table_name, key_value_pair_string)
        if self.debug:
           print "prepared_sql_statement : ", prepared_sql_statement

        result = []
 
        try:
            cursor.execute(prepared_sql_statement, )
        except Exception as e:
            print e
            return result

        for row in cursor:
            answer = obj.__class__()
            for key in obj.__dict__.keys():
                answer.__dict__[key] = row[key]
            
            result.append(answer)

        return result
            
    def all(self, obj, count = 1):
        connection, cursor = self.resolve_type(obj)

        if self.debug:
            print "getting all obj having schema {0} with keys".format(obj.__class__.__name__)
            for k in obj.__dict__.keys():
                print k,obj.__dict__[k]

        table_name = obj.__class__.__name__
        if self.debug:
            print "db name: " , table_name

        column_names = ",".join(obj.__dict__.keys())

        prepared_sql_statement = "select {0} from {1} ".format(column_names,table_name)
        if self.debug:
           print "prepared_sql_statement : ", prepared_sql_statement
           
        result = []

        try:
            cursor.execute(prepared_sql_statement, )
        except Exception as e:
            print e
            return result

        for row in cursor:
            answer = obj.__class__()
            for key in obj.__dict__.keys():
                answer.__dict__[key] = row[key]
            
            result.append(answer)

        return result
        
    def count(self, obj):
        connection, cursor = self.resolve_type(obj)

        if self.debug:
            print "getting obj count having schema {0} with keys".format(obj.__class__.__name__)

        table_name = obj.__class__.__name__
        if self.debug:
            print "db name: " , table_name

        prepared_sql_statement = "select count(*) from {0}".format(table_name)
        if self.debug:
           print "prepared_sql_statement : ", prepared_sql_statement

        result = -1

        try:
            cursor.execute(prepared_sql_statement, )
        except Exception as e:
            print e
            return result

        for row in cursor:
            result = row[0]

        if self.debug:
            print "Count : ", result

        return result    
        
